--- redirect_from: - "/04metrics/06combinedplot/combined" interact_link: content/04Metrics/06CombinedPlot/combined.ipynb kernel_name: python3 kernel_path: content/04Metrics/06CombinedPlot has_widgets: false title: |- Combined Plot pagenum: 8 prev_page: url: /04Metrics/04TotalNumAdd/totadd.html next_page: url: suffix: .ipynb search: comment: "***PROGRAMMATICALLY GENERATED, DO NOT EDIT. SEE ORIGINAL FILES IN /content***" ---
Combined Plot
from google.cloud import bigquery
from google.oauth2 import service_account
import plotly.graph_objects as go
import plotly.tools as tls

from plotly.offline import plot, iplot, init_notebook_mode
from IPython.core.display import display, HTML
init_notebook_mode(connected = True)
config={'showLink': False, 'displayModeBar': False}

credentials = service_account.Credentials.from_service_account_file(
    '')
project_id = ''
client = bigquery.Client(credentials= credentials,project=project_id)

query_job = client.query("""
SELECT  bt.*, ct.TotalNoTransactions as CashTransactions, lt.TotalNoTransactions as LiteTransactions,
btr.TotalBitcoins as TotalBitcoins, ctr.TotalBitcoins as TotalCash, ltr.TotalBitcoins as TotalLitecoins,
bad.distinct_addresses as BitcoinAddresses, cad.distinct_addresses as CashAddresses, lad.distinct_addresses as LitecoinAddresses
FROM `intricate-yew-283611.Blockchain.Bitcoin_Transactions_Lite` as bt
left join `intricate-yew-283611.Blockchain.Cash_Transactions_Lite` as ct on bt.Datum = ct.Datum
left join `intricate-yew-283611.Blockchain.Litecoin_Transactions_Lite` as lt on bt.Datum = lt.Datum
left join `intricate-yew-283611.Blockchain.Bitcoins_Traded` as btr on bt.Datum = btr.Datum
left join `intricate-yew-283611.Blockchain.Cash_Traded` as ctr on bt.Datum = ctr.Datum
left join `intricate-yew-283611.Blockchain.Litecoin_Traded` as ltr on bt.Datum = ltr.Datum
left join `intricate-yew-283611.Blockchain.Bitcoin_activeAd` as bad on bt.Day = bad.Day and bt.Month = bad.Month and bt.Year = bad.Year
left join `intricate-yew-283611.Blockchain.cash_activeAd` as cad on bt.Day = cad.Day and bt.Month = cad.Month and bt.Year = cad.Year
left join `intricate-yew-283611.Blockchain.Litecoin_activeAd` as lad on bt.Datum = cast(lad.Datum as string)
Order By Year, Month, Day
 
 """)
results = query_job.result()

results_df = results.to_dataframe()
print(results_df)

      Day  Month  Year      Datum  TotalNoTransactions  CashTransactions  \
0       3      1  2009   2009-1-3                    1               NaN   
1       9      1  2009   2009-1-9                   14               NaN   
2      10      1  2009  2009-1-10                   61               NaN   
3      11      1  2009  2009-1-11                   93               NaN   
4      12      1  2009  2009-1-12                  101               NaN   
...   ...    ...   ...        ...                  ...               ...   
4204   13      7  2020  2020-7-13               310227           15976.0   
4205   14      7  2020  2020-7-14               359847           17389.0   
4206   15      7  2020  2020-7-15               316064           15482.0   
4207   16      7  2020  2020-7-16               345849           14520.0   
4208   17      7  2020  2020-7-17               215793            8760.0   

      LiteTransactions      TotalBitcoins          TotalCash  \
0                  NaN       50.000000000               None   
1                  NaN      700.000000000               None   
2                  NaN     3050.000000000               None   
3                  NaN     4650.000000000               None   
4                  NaN     4879.000000000               None   
...                ...                ...                ...   
4204           36640.0  1223951.560331680  1180740.693013960   
4205           37498.0   910650.469210690   875385.897658970   
4206           37433.0   833824.640291050   573124.265601250   
4207           38719.0   984704.275211660   492445.239097630   
4208           29875.0   787149.051975400   368647.108556470   

         TotalLitecoins  BitcoinAddresses  CashAddresses  LitecoinAddresses  
0                  None                 1            NaN                NaN  
1                  None                14            NaN                NaN  
2                  None                61            NaN                NaN  
3                  None                93            NaN                NaN  
4                  None               102            NaN                NaN  
...                 ...               ...            ...                ...  
4204  4088025.242283000            913626        73256.0            79439.0  
4205  2738068.367728920           1008030        95582.0           100687.0  
4206  3122215.091918510            972945        64146.0            87232.0  
4207  5665445.941763870            974469        67501.0            83412.0  
4208  5062431.687269510            858601        62183.0            83708.0  

[4209 rows x 13 columns]

fig2 = go.FigureWidget()

fig2.add_trace(go.Scatter(x = results_df['Datum'], y = results_df['TotalNoTransactions'],name = 'Bitcoin',line = dict(color = 'Light Blue')))
fig2.add_trace(go.Scatter(x = results_df['Datum'], y = results_df['CashTransactions'], name = 'Bitcoin Cash',line = dict(color = 'rgb(237, 97, 97)')))
fig2.add_trace(go.Scatter(x = results_df['Datum'], y = results_df['LiteTransactions'], name = 'Litecoin', line = dict(color = 'Light Green')))
 
names = ["Bitcoin", "Bitcoin Cash", "Litecoin"]
lst = ['TotalBitcoins','TotalCash','TotalLitecoins']
colors = ['Light Blue','rgb(237, 97, 97)','Light Green']
for num in range(0,3):
    a = results_df[lst[num]].tolist()
    b = []
    for x in a:
        if (x is not None):
            b.append(float(x))
        else:
            b.append(x)
    fig2.add_trace(go.Scatter(x = results_df['Datum'], y = b, name = names[num], line = dict(color =colors[num] ), visible = False))


fig2.add_trace(go.Scatter(visible = False,x = results_df['Datum'], y = results_df['BitcoinAddresses'],name = 'Bitcoin',line = dict(color = 'Light Blue')))
fig2.add_trace(go.Scatter(visible = False,x = results_df['Datum'], y = results_df['CashAddresses'], name = 'Bitcoin Cash',line = dict(color = 'rgb(237, 97, 97)')))
fig2.add_trace(go.Scatter(visible = False,x = results_df['Datum'], y = results_df['LitecoinAddresses'], name = 'Litecoin', line = dict(color = 'Light Green')))
 

fig2.update_layout(paper_bgcolor = 'White',xaxis_title="Date",
    yaxis_title='Number of Transactions',plot_bgcolor="#FFFFFF",hovermode="x",
    hoverdistance=100, 
    spikedistance=1000,
     updatemenus=[
                    dict(
                        active=0,
                        buttons=list([
                       
                            dict(label="Number of Transactions",
                             method="update",
                             args=[{"visible": 
                            [True,True,True,False, False,False, False, False, False]},
                               {"title": "",
                                'yaxis': {'title': 'Number of Transactions', 'ticks' : 'outside', 'showline': True, 'linecolor': 'black' }}
                                  ]),
                            
                        dict(label=" Coins Traded",
                             method="update",
                             args=[{"visible": 
                                  [ False, False, False, True,True,True,False,False,False]},
                                  {"title": "",
                                   'yaxis': {'title': 'Number of Coins Traded', 'ticks' : 'outside', 'showline': True, 'linecolor': 'black' }}
                                   
                                ]),
                            
                            dict(label=" Active Addresses",
                             method="update",
                             args=[{"visible": 
                                  [ False, False, False,False,False,False,True,True,True]},
                                  {"title": "",
                                   'yaxis': {'title': 'Number of Active Addresses', 'ticks' : 'outside', 'showline': True, 'linecolor': 'black' }}
                                   
                                ]),
                        
               
            ]),
                        x=0.2,
                        y = 1.2
                        
        )
]
)
fig2.update_xaxes(showspikes=True,spikethickness=2,
        spikedash="dot",
        spikecolor="#999999",
        spikemode="across",showgrid=False,ticks = 'outside', showline=True, linecolor='black',rangeslider_visible=True,
                rangeselector=dict(
        buttons=list([
            dict(count=1, label="1m", step="month", stepmode="backward"),
            dict(count=6, label="6m", step="month", stepmode="backward"),
            dict(count=1, label="YTD", step="year", stepmode="todate"),
            dict(count=1, label="1y", step="year", stepmode="backward"),
            dict(step="all")
        ]), x=0.9,
            y = 1.2
    ))
fig2.update_yaxes(autorange = True, fixedrange= False,showgrid=False, ticks = 'outside', showline=True, linecolor='black')



plot(fig2, filename = 'fig2.html', config = config)
#ThebeLab
display(HTML('fig2.html'))